﻿////****************************************************************************
////
//// Descriptive Name = A  data access wrapper library for 
//// IBM.Data.DB2.iSeries provider.
//// Short Name = ISeriesDataProxy
//// Function = A data access proxy for client applications. 
////
//// Copyright for IBM.Data.DB2.iSeries and other IBM libraries used belongs to 
//// IBM Corp 2008
////
////****************************************************************************
////============================================================================
//// Copyright © Shibu Raj - shibu.raj@gmail.com.  All Rights Reserved.
//// This code released under the terms of the 
//// GNU General Public License version 2 (GPLv2)
//// (http://isereisdataproxy.codeplex.com/license)
////============================================================================

namespace ISeriesDataProxy
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Globalization;
    using System.Transactions;
    using IBM.Data.DB2.iSeries;

    /// <summary>
    /// Represents the iSeries DB2 Database.
    /// </summary>
    public class Db2Database
    {
        /// <summary>
        /// Variable represents the connection string for the Db2Database.
        /// </summary>
        private string connectionString;

        /// <summary>
        /// Varibale represents the Db2Database instance factory.
        /// </summary>
        private iDB2Factory providerFactory;

        /// <summary>
        /// Initializes a new instance of the Db2Database class.
        /// </summary>
        /// <param name="connectString">Connection string for initializing the connection.</param>
        public Db2Database(string connectString)
        {
            if (String.IsNullOrEmpty(connectString))
            {
                throw new ArgumentException("ConnectionString value needs to be specified", "ConnectionString");
            }

            this.connectionString = this.GetConnectionString(connectString);
            this.providerFactory = iDB2Factory.Instance;
        }

        /// <summary>
        /// Update behaviour supported values.
        /// </summary>
        public enum UpdateBehavior
        {
            /// <summary>
            /// Standard Update Behaviour.
            /// </summary>
            Standard = 0,

            /// <summary>
            /// Update and Continue behaviour.
            /// </summary>
            Continue = 1,
            
            /// <summary>
            /// Transactional Update.
            /// </summary>
            Transactional = 2
        }
        
        /// <summary>
        /// Gets the connection string associated with the database object.
        /// </summary>
        protected internal string ConnectionString
        {
            get { return this.connectionString.ToString().Trim(); }
        }

        /// <summary>
        /// Instantiates a new connection and returns the same.
        /// </summary>
        /// <returns>A new connection to the database.</returns>
        public DbConnection CreateConnection()
        {
            iDB2Connection connection =
                (iDB2Connection)this.providerFactory.CreateConnection();
            connection.ConnectionString = this.connectionString;
            return connection;
        }
        
        /// <summary>
        /// Returns the Stored Procudure Commond for the databse from the 
        /// procedure name and sets the parameter values.        /// 
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored proc.</param>
        /// <param name="parameterValues">Parameters for the proc.</param>
        /// <returns>iSeries DB2 Command object for the proc.</returns>
        public DbCommand GetStoredProcCommand(
            string storedProcedureName, params object[] parameterValues)
        {
            if (string.IsNullOrEmpty(storedProcedureName))
            {
                throw new ArgumentException("The value cannot be null or empty", "storedProcedureName");
            }

            iDB2Command command =
                (iDB2Command)this.CreateCommandByCommandType(CommandType.StoredProcedure, storedProcedureName);
            this.DiscoverParameters(command);
            if (!this.SameNumberofParametersAndValues(command, parameterValues))
            {
                throw new InvalidOperationException("The number of parameter values supplied is not equal to the parameter count of the command object.");
            }

            this.AssignParameterValues(command, parameterValues);
            return command;
        }

        /// <summary>
        /// Returns the iDB2 Command object for the stored proc name provided.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored proc.</param>
        /// <returns>iDB2 Command object for the procedure.</returns>
        public DbCommand GetStoredProcCommand(string storedProcedureName)
        {
            if (string.IsNullOrEmpty(storedProcedureName))
            {
                throw new ArgumentException("The value cannot be null or empty", "strored procedure name");
            }

            iDB2Command command =
                (iDB2Command)this.CreateCommandByCommandType(CommandType.StoredProcedure, storedProcedureName);
            return command;
        }

        /// <summary>
        /// Returns the stored procedure command from the database with source 
        /// coloumns.
        /// </summary>
        /// <param name="storedProcedureName">
        /// Name of the stored procedure to be executed.
        /// </param>
        /// <param name="sourceColumns">
        /// Source coloumn names for the given stored procedure.
        /// </param>
        /// <returns>A command for executing the stored procedure.</returns>
        public DbCommand GetStoredProcCommandWithSourceColumns(
            string storedProcedureName, params string[] sourceColumns)
        {
            if (string.IsNullOrEmpty(storedProcedureName))
            {
                throw new ArgumentException("The stored procedure name cannot be an empty or null string.");
            }

            if (sourceColumns == null)
            {
                throw new ArgumentNullException("sourceColumns");
            }

            if (sourceColumns.Length == 0)
            {
                throw new ArgumentException("No Source column values supplied");
            }

            iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName);
            using (iDB2Connection connection = (iDB2Connection)this.CreateConnection())
            {
                command.Connection = connection;
                this.DiscoverParameters(command);
            }

            int index = 0;
            foreach (iDB2Parameter parameter in command.Parameters)
            {
                if ((parameter.Direction == ParameterDirection.Input) |
                    (parameter.Direction == ParameterDirection.InputOutput))
                {
                    parameter.SourceColumn = sourceColumns[index];
                    index++;
                }
            }

            return command;
        }

        /// <summary>
        /// Creates and returns a commond object for the given sql string.
        /// </summary>
        /// <param name="query">An SQL Statement to be executed.</param>
        /// <returns>
        /// A command object for executing the given SQL statement.
        /// </returns>
        public DbCommand GetSqlStringCommand(string query)
        {
            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentException("The query cannot be an empty string.");
            }

            return this.CreateCommandByCommandType(CommandType.Text, query);
        }
        
        #region Parameter Related Calls

        /// <summary>
        /// Retrives the value of the paramter from the given command object.
        /// </summary>
        /// <param name="command"> A DB2Command object from which the parameter values needs to be retrieved.</param>
        /// <param name="name">Name of the parameter for which the value needs to be retrieved.</param>
        /// <returns>The value of the given parameter. </returns>
        public object GetParameterValue(DbCommand command, string name)
        {
            return ((iDB2Command)command).Parameters[name].Value;
        }

        /// <summary>
        /// Assigns the value of the parameter.
        /// </summary>
        /// <param name="command">DB2Command object on which the parameter value needs to be set.</param>
        /// <param name="parameterName">Name of the parameter for which the value needs to be set.</param>
        /// <param name="value">Value of the parameter.</param>
        public void SetParameterValue(
            DbCommand command, string parameterName, object value)
        {
            command.Parameters[parameterName].Value = (value == null) ? DBNull.Value : value;
        }

        /// <summary>
        /// Retrieves/discovers the parameters for the given command object.
        /// </summary>
        /// <param name="command">A Db2Command object for which the parameter needs to be retrieved. </param>
        public void DiscoverParameters(DbCommand command)
        {
            using (ConnectionWrapper wrapper = this.GetOpenConnection())
            {
                using (iDB2Command command2 =
                    this.CreateCommandByCommandType(command.CommandType, command.CommandText))
                {
                    command2.Connection = wrapper.Connection;
                    this.DeriveParameters(command2);
                    foreach (iDB2Parameter param in command2.Parameters)
                    {
                        iDB2Parameter parameter2 = (iDB2Parameter)((ICloneable)param).Clone();
                        command.Parameters.Add(parameter2);
                    }
                }
            }
        }

        /// <summary>
        /// Adds a parameter to the given Db2Command object.
        /// </summary>
        /// <param name="command">A Db2Command object to which parameter needs to be added.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>
        /// <param name="size">Data size of the parameter.</param>
        /// <param name="direction">Specifies the direction of the parameter.</param>
        /// <param name="nullable">Does the parameter accepts null value or not.</param>
        /// <param name="precision">Precission of the parameter object.</param>
        /// <param name="scale">Scale for the parameter.</param>
        /// <param name="sourceColumn">Source coloun in the command object to which the parameter maps to.</param>
        /// <param name="sourceVersion">Version of the source.</param>
        /// <param name="value">Value for the parameter.</param>
        public void AddParameter(
            DbCommand command, string name, DbType dbType, int size,
            ParameterDirection direction, bool nullable, byte precision,
            byte scale, string sourceColumn, DataRowVersion sourceVersion,
            object value)
        {
            DbParameter parameter = this.CreateParameter(
                name, dbType, size, direction, nullable, precision,
                scale, sourceColumn, sourceVersion, value);
            command.Parameters.Add(parameter);
        }

        /// <summary>
        /// Adds a parameter to the given Db2Command object.
        /// </summary>
        /// <param name="command">A Db2Command object to which parameter needs to be added.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>        
        /// <param name="direction">Specifies the direction of the parameter.</param>
        /// <param name="sourceColumn">Source coloun in the command object to which the parameter maps to.</param>
        /// <param name="sourceVersion">Version of the source.</param>
        /// <param name="value">Value for the parameter.</param>
        public void AddParameter(
            DbCommand command, string name, DbType dbType,
            ParameterDirection direction, string sourceColumn,
            DataRowVersion sourceVersion, object value)
        {
            this.AddParameter(
                command, name, dbType, 0, direction, false,
                0, 0, sourceColumn, sourceVersion, value);
        }

        /// <summary>
        /// Adds a parameter to the given Db2Command object.
        /// </summary>
        /// <param name="command">A Db2Command object to which parameter needs to be added.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param> 
        /// <param name="sourceColumn">Source coloun in the command object to which the parameter maps to.</param>
        /// <param name="sourceVersion">Version of the source.</param>
        public void AddInParameter(
            DbCommand command, string name, DbType dbType, string sourceColumn,
            DataRowVersion sourceVersion)
        {
            this.AddParameter(
                command, name, dbType, ParameterDirection.Input,
                sourceColumn, sourceVersion, null);
        }

        /// <summary>
        /// Adds a parameter to the given Db2Command object.
        /// </summary>
        /// <param name="command">A Db2Command object to which parameter needs to be added.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>
        /// <param name="value">Value for the parameter.</param>
        public void AddInParameter(
            DbCommand command, string name, DbType dbType, object value)
        {
            this.AddParameter(
                command, name, dbType, ParameterDirection.Input,
                string.Empty, DataRowVersion.Default, value);
        }

        /// <summary>
        /// Adds a parameter to the given Db2Command object.
        /// </summary>
        /// <param name="command">A Db2Command object to which parameter needs to be added.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>
        public void AddInParameter(DbCommand command, string name, DbType dbType)
        {
            this.AddParameter(
                command, name, dbType, ParameterDirection.Input,
                string.Empty, DataRowVersion.Default, null);
        }

        /// <summary>
        /// Adds an out parameter to the given command object.
        /// </summary>
        /// <param name="command">Db2 Command object to which the parameter needs to be attached.</param>
        /// <param name="name">Name of the paramater.</param>
        /// <param name="dbtype">Data type of the paramater.</param>
        /// <param name="size">Data size of the parameter.</param>
        public void AddOutParameter(
            DbCommand command, string name, DbType dbtype, int size)
        {
            this.AddParameter(
                command, name, dbtype, size, ParameterDirection.Output,
                true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value);
        }
        
        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="command">Db2Command object on which the non query statement needs to be executed.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(DbCommand command)
        {
            using (ConnectionWrapper warpper = this.GetOpenConnection())
            {
                this.PrepareCommand(command, warpper.Connection);
                return this.DoExecuteNonQuery((iDB2Command)command);
            }
        }

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="commandType">Specifies the type of the command to be used for execution.</param>
        /// <param name="commandText">Statement text for creating the command object for execution.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteNonQuery(command);
            }
        }

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="command">DB2Command object on which the statement needs to be executed.</param>
        /// <param name="transaction">Transaction to which this execution needs to be enlisted to.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(DbCommand command, DbTransaction transaction)
        {
            this.PrepareCommand(command, transaction);
            return this.DoExecuteNonQuery((iDB2Command)command);
        }

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure that needs to be executed.</param>
        /// <param name="parameterValues">Parameter values for the stored procedure that needs to be executed.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(string storedProcedureName,
            params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteNonQuery(command);
            }
        }

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="transaction">Transaction to which this execution needs to be enlisted to.</param>
        /// <param name="commandType">Type of the command on which the command text needs to be executed.</param>
        /// <param name="commandText">SQL command text for execution.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(DbTransaction transaction, CommandType commandType,
            string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteNonQuery(command, transaction);
            }
        }

        /// <summary>
        /// Executes a non query statement using the supplied command object.
        /// </summary>
        /// <param name="transaction">Transaction to which this execution needs to be enlisted to.</param>
        /// <param name="storedProcedureName">Name of the stored procedure for execution.</param>
        /// <param name="parameterValues">Parameters values for the stored procedure.</param>
        /// <returns>Returns the number of rows affected by the execution of the statement.</returns>
        public int ExecuteNonQuery(
            DbTransaction transaction, string storedProcedureName,
            params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteNonQuery(command, transaction);
            }
        }

        #endregion

        #region ExecuteReader

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="command">Db2Command object for execution of command.</param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(DbCommand command)
        {
            iDB2DataReader reader;
            ConnectionWrapper openConnection = this.GetOpenConnection(false);
            try
            {
                this.PrepareCommand(command, openConnection.Connection);
                //// if there is a transaction, dont close the connection.
                if (Transaction.Current != null)
                {
                    return (IDataReader)this.DoExecuteReader((iDB2Command)command, CommandBehavior.Default);
                }
                //// If there is no running transactions, close the connection after execution.
                reader = this.DoExecuteReader((iDB2Command)command, CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                if (openConnection.Connection.State == ConnectionState.Open)
                {
                    openConnection.Connection.Close();
                }
                
                openConnection.Dispose();
                GC.SuppressFinalize(openConnection);
                throw;
            }

            return reader;
        }

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="commandType">Type of the command object that needs to be created for execution.</param>
        /// <param name="commandText">SQL statement text for creating the command object. </param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteReader(command);
            }
        }

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="command">Db2Command object for execution.</param>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(DbCommand command, DbTransaction transaction)
        {
            this.PrepareCommand(command, transaction);
            return this.DoExecuteReader((iDB2Command)command, CommandBehavior.Default);
        }

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure from which command object needs to be created.</param>
        /// <param name="parameterValues">Parameter values for the stored procedure.</param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(string storedProcedureName, params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteReader(command);
            }
        }

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <param name="commandType">Type of the command object that needs to be created for execution.</param>
        /// <param name="commandText">SQL statement text for creating the command object.</param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteReader(command, transaction);
            }
        }

        /// <summary>
        /// Executes the command against the database and returns a data reader.
        /// </summary>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <param name="storedProcedureName">Name of the stored procedure from which command object needs to be created.</param>
        /// <param name="parameterValues">Parameter values for the stored procedure.</param>
        /// <returns>Returns a data reader for the command object supplied.</returns>
        public IDataReader ExecuteReader(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteReader(command, transaction);
            }
        }

        #endregion

        #region ExecuteScalar

        /// <summary>
        /// Executes the command againt the database.
        /// </summary>
        /// <param name="command">Db2 Command Object for the execution.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(DbCommand command)
        {
            if (command == null)
            {
                throw new ArgumentException("command", "Command Object cannot be null");
            }

            using (ConnectionWrapper wrapper = this.GetOpenConnection())
            {
                this.PrepareCommand(command, wrapper.Connection);
                return this.DoExecuteScalar((iDB2Command)command);
            }
        }

        /// <summary>
        /// Executes the scalar command agaist the database.
        /// </summary>
        /// <param name="commandType">Type of the command object that needs to be created/used for execution.</param>
        /// <param name="commandText">SQL statement text for creating the command object.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteScalar(command);
            }
        }

        /// <summary>
        /// Executes the scalar command against the database.
        /// </summary>
        /// <param name="command">>Db2 Command Object for the execution.</param>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(DbCommand command, DbTransaction transaction)
        {
            this.PrepareCommand(command, transaction);
            return this.DoExecuteScalar((iDB2Command)command);
        }

        /// <summary>
        /// Executes the scalar command against the database.
        /// </summary>
        /// <param name="storedProcedureName">
        /// Name of the stored procedure from which command object needs to be created.
        /// </param>
        /// <param name="parameterValues">Parameter values for the stored procedure.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteScalar(command);
            }
        }

        /// <summary>
        /// Executes the scalar command against the database.
        /// </summary>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <param name="commandType">Type of the command object that needs to be created/used for execution.</param>
        /// <param name="commandText">SQL statement text for creating the command object.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                (iDB2Command)this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteScalar(command, transaction);
            }
        }

        /// <summary>
        /// Executes the scalar command against the database.
        /// </summary>
        /// <param name="transaction">Database transaction to which the execution needs to be enlisted to.</param>
        /// <param name="storedProcedureName">Name of the stored procedure from which command object needs to be created.</param>
        /// <param name="parameterValues">Parameter values for the stored procedure.</param>
        /// <returns>Returns the scaler result of the command execution.</returns>
        public object ExecuteScalar(DbTransaction transaction, string storedProcedureName, params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteScalar(command, transaction);
            }
        }

        #endregion

        #region Load DataSet

        /// <summary>
        /// Loads the data into the provided dataset from the given table using
        /// command object provided.
        /// </summary>
        /// <param name="command">Db2 Command object.</param>
        /// <param name="dataSet">An instahce of the dataset to collect the data.</param>
        /// <param name="tableName">Name of the Db2 table from which data needs to be loaded.</param>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string tableName)
        {
            this.LoadDataSet(command, dataSet, new string[] { tableName });
        }

        /// <summary>
        /// Loads the data into the provided dataset from the given tables using the command object provided.
        /// </summary>
        /// <param name="command">Db2 Command object.</param>
        /// <param name="dataSet">An instance of the dataset to which the data needs to be collected.</param>
        /// <param name="tableNames">Names of the table from which data needs to be fetched.</param>
        public void LoadDataSet(DbCommand command, DataSet dataSet, string[] tableNames)
        {
            using (ConnectionWrapper wrapper = this.GetOpenConnection())
            {
                this.PrepareCommand(command, wrapper.Connection);
                this.DoLoadDataSet((iDB2Command)command, dataSet, tableNames);
            }
        }

        /// <summary>
        /// Loads the data into the provided dataset from the given tables using the command text provided..
        /// </summary>
        /// <param name="commandType">Type of the command text that is provided</param>
        /// <param name="commandText">Actual command text for the command object.</param>
        /// <param name="dataSet">An instance of the dataset to which data needs to be collected.</param>
        /// <param name="tableNames">Names of the table from which data needs to be collected.</param>
        public void LoadDataSet(
            CommandType commandType, string commandText,
            DataSet dataSet, string[] tableNames)
        {
            using (iDB2Command command = this.CreateCommandByCommandType(commandType, commandText))
            {
                this.LoadDataSet(commandText, dataSet, tableNames);
            }
        }

        /// <summary>
        /// Loads the data into the provided dataset for the given tables using the command object supplied.
        /// </summary>
        /// <param name="command">An instance of the Db2 Command object for fetching data.</param>
        /// <param name="dataSet">An instance of the dataset to which the data needs to be loaded.</param>
        /// <param name="tableName">Name of the Db2 table from which data needs to be loaded.</param>
        /// <param name="transaction">Transaction to which the load dataset operation to be enlisted to.</param>
        public void LoadDataSet(
            DbCommand command, DataSet dataSet,
            string tableName, DbTransaction transaction)
        {
            this.LoadDataSet(command, dataSet, new string[] { tableName }, transaction);
        }

        /// <summary>
        /// Loads the data into the provided dataset for the given tables using the command object.
        /// </summary>
        /// <param name="command">An instance of the Db2 Command object for fetching the data.</param>
        /// <param name="dataSet">An instance of the dataset to which the data needs to be loaded.</param>
        /// <param name="tableNames">Names of the table from which data needs to be loaded.</param>
        /// <param name="transaction">Db2 Transaction to which the load dataset operation needs to be enlisted to.</param>
        public void LoadDataSet(
            DbCommand command, DataSet dataSet,
            string[] tableNames, DbTransaction transaction)
        {
            this.PrepareCommand(command, transaction);
            this.DoLoadDataSet((iDB2Command)command, dataSet, tableNames);
        }

        /// <summary>
        /// Loads the data into the provided dataset for the given tables using the provided stored procedure.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored proc to be executed to load the dataset.</param>
        /// <param name="dataSet">An isntance of dataset to which the data needs to be loaded.</param>
        /// <param name="tableNames">Names of the table from whcih data needs to be loaded.</param>
        /// <param name="parameterValues">Values for the stored proc parameters.</param>
        public void LoadDataSet(
            string storedProcedureName, DataSet dataSet,
            string[] tableNames, params object[] parameterValues)
        {
            using (DbCommand command =
                this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                this.LoadDataSet(command, dataSet, tableNames);
            }
        }

        /// <summary>
        /// Loads the data into the provided dataset for the given tables using the provided command text.
        /// </summary>
        /// <param name="transaction">DB2 Transaction to which the load operation to be enlisted to.</param>
        /// <param name="commandType">Type of the command object that needs to be created from command text.</param>
        /// <param name="commandText">Statement text for the command object.</param>
        /// <param name="dataSet">An insatcne of the dataset object that needs to be loaded.</param>
        /// <param name="tableNames">Names of the table to which data needs to be loaded.</param>
        public void LoadDataSet(
            DbTransaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames)
        {
            using (DbCommand command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                this.LoadDataSet(command, dataSet, tableNames, transaction);
            }
        }

        /// <summary>
        /// Loads the data into the provided dataset for the given table names using the stored procedure provided.
        /// </summary>
        /// <param name="transaction">Db2 Transaction to which the load operation needs to be enlisted to.</param>
        /// <param name="storedProcedureName">Stored Proc name that needs to be used for loading.</param>
        /// <param name="dataSet">An instance of dataset that needs to be loaded.</param>
        /// <param name="tableNames">Names of the tables that needs to be loaded in the dataset</param>
        /// <param name="parameterValues">Values for the stored proc parameters.</param>
        public void LoadDataSet(
            DbTransaction transaction, string storedProcedureName,
            DataSet dataSet, string[] tableNames, params object[] parameterValues)
        {
            using (DbCommand command =
                this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                this.LoadDataSet(command, dataSet, tableNames, transaction);
            }
        }

        #endregion

        #region

        /// <summary>
        /// Executes the command text in the command object and returns Dataset.
        /// </summary>
        /// <param name="command">Db2 Command object that needs to be executed.</param>
        /// <returns>An instance of the dataset.</returns>
        public DataSet ExecuteDataSet(DbCommand command)
        {
            DataSet dataset = new DataSet();
            dataset.Locale = CultureInfo.InvariantCulture;
            this.LoadDataSet(command, dataset, "Table");
            return dataset;
        }

        /// <summary>
        /// Executes the command text provided and returns a dataset.
        /// </summary>
        /// <param name="commandType">Type of Db2 Command text that is provided.</param>
        /// <param name="commandText">Command Text for the Db2 Command Object.</param>
        /// <returns>An instance of the dataset object.</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteDataSet(command);
            }
        }

        /// <summary>
        /// Executes the command object with in the context of the existing transaction.
        /// </summary>
        /// <param name="command">An instance of the command object that needs to be executed.</param>
        /// <param name="transaction">An instance of the DB2 transaction to which
        /// the current command needs to be enlisted to.</param>
        /// <returns>An instance of the dataset object.</returns>
        public DataSet ExecuteDataSet(DbCommand command, DbTransaction transaction)
        {
            DataSet dataset = new DataSet();
            dataset.Locale = CultureInfo.InvariantCulture;
            this.LoadDataSet(command, dataset, "Table", transaction);
            return dataset;
        }

        /// <summary>
        /// Executes the given stored procedure and return a dataset.
        /// </summary>
        /// <param name="storedProcedureName">Name of the stored procedure that needs to be executed.</param>
        /// <param name="parameterValues">Values for the given stored procedure.</param>
        /// <returns>An instance opf the dataset object.</returns>
        public DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
        {
            using (iDB2Command command =
                (iDB2Command)this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteDataSet(command);
            }
        }

        /// <summary>
        /// Executes the give command text with in the context of the given transaction.
        /// </summary>
        /// <param name="transaction">An instance of the Db2 Transaction to which it needs to be enlisted to.</param>
        /// <param name="commandType">Type of the DB2 command object that needs to be created.</param>
        /// <param name="commandText">Command Text that needs to be attached to the command object.</param>
        /// <returns>An instance of the dataset object.</returns>
        public DataSet ExecuteDataSet(
            DbTransaction transaction, CommandType commandType,
            string commandText)
        {
            using (iDB2Command command =
                this.CreateCommandByCommandType(commandType, commandText))
            {
                return this.ExecuteDataSet(command, transaction);
            }
        }

        /// <summary>
        /// Executes the given stored proc with in the context of the given transaction.
        /// </summary>
        /// <param name="transaction">An instance of the DB2 transaction on which we need to run the stored proc.</param>
        /// <param name="storedProcedureName">Name of the stored procedure.</param>
        /// <param name="parameterValues">Values of the stored procedure parameters.</param>
        /// <returns>An instance of the dataset.</returns>
        public DataSet ExecuteDataSet(
            DbTransaction transaction, string storedProcedureName,
            params object[] parameterValues)
        {
            using (DbCommand command =
                this.GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return this.ExecuteDataSet(command, transaction);
            }
        }

        #endregion

        #region Update Dataset

        /// <summary>
        /// Updates the supplied dataset
        /// </summary>
        /// <param name="dataSet">An instance of the dataset that needs to be used for updating</param>
        /// <param name="tableName">Names of the tables inside dataset that needs to be updated.</param>
        /// <param name="insertCommand">Command object representing the insert action.</param>
        /// <param name="updateCommand">Command object representing the update action.</param>
        /// <param name="deleteCommand">Command object representing the delete action.</param>
        /// <param name="updateBehavior">Represents the behaviour of the update dataset.</param>
        /// <returns>Returns the number of rows affected.</returns>
        public int UpdateDataSet(
            DataSet dataSet, string tableName, DbCommand insertCommand,
            DbCommand updateCommand, DbCommand deleteCommand,
            UpdateBehavior updateBehavior)
        {
            return this.UpdateDataSet(dataSet, tableName, insertCommand,
                updateCommand, deleteCommand, updateBehavior, null);
        }

        /// <summary>
        /// Updates the supplied dataset
        /// </summary>
        /// <param name="dataSet">An instance of the dataset that needs to be used for updating</param>
        /// <param name="tableName">Names of the tables inside dataset that needs to be updated.</param>
        /// <param name="insertCommand">Command object representing the insert action.</param>
        /// <param name="updateCommand">Command object representing the update action.</param>
        /// <param name="deleteCommand">Command object representing the delete action.</param>
        /// <param name="transaction">An instance of the transaction to which this update has to be enlisted to.</param>
        /// <returns>Returns the number of rows affected.</returns>
        public int UpdateDataSet(
            DataSet dataSet, string tableName, DbCommand insertCommand,
            DbCommand updateCommand, DbCommand deleteCommand,
            DbTransaction transaction)
        {
            return this.UpdateDataSet(dataSet, tableName, insertCommand,
                updateCommand, deleteCommand, transaction, null);
        }

        /// <summary>
        /// Updates the supplied dataset
        /// </summary>
        /// <param name="dataSet">An instance of the dataset that needs to be used for updating</param>
        /// <param name="tableName">Names of the tables inside dataset that needs to be updated.</param>
        /// <param name="insertCommand">Command object representing the insert action.</param>
        /// <param name="updateCommand">Command object representing the update action.</param>
        /// <param name="deleteCommand">Command object representing the delete action.</param>
        /// <param name="updateBehavior">Represents the behaviour of the update dataset.</param>
        /// <param name="updateBatchSize">Size for the batch update.</param>
        /// <returns>Returns the number of rows affected.</returns>
        public int UpdateDataSet(
            DataSet dataSet, string tableName, DbCommand insertCommand,
            DbCommand updateCommand, DbCommand deleteCommand,
            UpdateBehavior updateBehavior, int? updateBatchSize)
        {
            using (ConnectionWrapper wrapper = this.GetOpenConnection())
            {
                if ((updateBehavior == UpdateBehavior.Transactional) &&
                    (Transaction.Current == null))
                {
                    iDB2Transaction transaction =
                        this.BeginTransaction(wrapper.Connection);
                    try
                    {
                        int num =
                            this.UpdateDataSet(dataSet, tableName,
                            insertCommand, updateCommand, deleteCommand,
                            transaction, updateBatchSize);
                        this.CommitTransaction(transaction);
                        return num;
                    }
                    catch (Exception)
                    {
                        this.RollbackTransaction(transaction);
                        throw;
                    }
                }

                if (insertCommand != null)
                {
                    this.PrepareCommand(insertCommand, wrapper.Connection);
                }

                if (updateCommand != null)
                {
                    this.PrepareCommand(updateCommand, wrapper.Connection);
                }

                if (deleteCommand != null)
                {
                    this.PrepareCommand(deleteCommand, wrapper.Connection);
                }

                return this.DoUpdateDataSet(updateBehavior, dataSet, tableName,
                    (iDB2Command)insertCommand, (iDB2Command)updateCommand,
                    (iDB2Command)deleteCommand, updateBatchSize);
            }
        }

        /// <summary>
        /// Updates the supplied dataset
        /// </summary>
        /// <param name="dataSet">An instance of the dataset that needs to be used for updating</param>
        /// <param name="tableName">Names of the tables inside dataset that needs to be updated.</param>
        /// <param name="insertCommand">Command object representing the insert action.</param>
        /// <param name="updateCommand">Command object representing the update action.</param>
        /// <param name="deleteCommand">Command object representing the delete action.</param>
        /// <param name="transaction">An instance of the transaction to which this update has to be enlisted to.</param>
        /// <param name="updateBatchSize">Size for the batch update.</param>
        /// <returns>Returns the number of rows affected.</returns>
        public int UpdateDataSet(
            DataSet dataSet, string tableName, DbCommand insertCommand,
            DbCommand updateCommand, DbCommand deleteCommand,
            DbTransaction transaction, int? updateBatchSize)
        {
            if (insertCommand != null)
            {
                this.PrepareCommand(insertCommand, transaction);
            }

            if (updateCommand != null)
            {
                this.PrepareCommand(updateCommand, transaction);
            }

            if (deleteCommand != null)
            {
                this.PrepareCommand(deleteCommand, transaction);
            }

            return this.DoUpdateDataSet(UpdateBehavior.Transactional, dataSet,
                tableName, (iDB2Command)insertCommand,
                (iDB2Command)updateCommand, (iDB2Command)deleteCommand,
                updateBatchSize);
        }

        #endregion

        #region Internal Member calls

        /// <summary>
        /// Creates a new open connection.
        /// </summary>
        /// <returns>An instance of Db2 connection object</returns>
        internal iDB2Connection GetNewOpenConnection()
        {
            iDB2Connection connection = null;
            try
            {
                connection = (iDB2Connection)this.CreateConnection();
                connection.Open();
            }
            catch (Exception)
            {
                if (connection != null)
                {
                    connection.Dispose();
                    GC.SuppressFinalize(connection);
                }

                throw;
            }

            return connection;
        }

        #endregion

        #region Protected Member Calls

        /// <summary>
        /// Creates a new DB2 Parameter object with the name supplied.
        /// </summary>
        /// <param name="name">Name of the parameter object.</param>
        /// <returns>An instance of the DB2 Parameter object.</returns>
        protected DbParameter CreateParameter(string name)
        {
            iDB2Parameter parameter =
                (iDB2Parameter)this.providerFactory.CreateParameter();
            parameter.ParameterName = name;
            return parameter;
        }

        /// <summary>
        /// Creates a new Db2 parameter object.
        /// </summary>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>
        /// <param name="size">Data Size of the parameter.</param>
        /// <param name="direction">Parameter direction</param>
        /// <param name="nullable">Is the parameter nullable or not.</param>
        /// <param name="precision">What is the data precession for the data type.</param>
        /// <param name="scale">Scale for the parameter value.</param>
        /// <param name="sourceColumn">Source coloumn represented by the parameter.</param>
        /// <param name="sourceVersion">Version of the source variable.</param>
        /// <param name="value">Value for the parameter object.</param>
        /// <returns>returns a new Db2 parameter object.</returns>
        protected DbParameter CreateParameter(
            string name, DbType dbType, int size, ParameterDirection direction,
            bool nullable, byte precision, byte scale, string sourceColumn,
            DataRowVersion sourceVersion, object value)
        {
            iDB2Parameter parameter = (iDB2Parameter)this.CreateParameter(name);
            this.ConfigureParameter(
                parameter, name, dbType, size, direction, nullable,
                precision, scale, sourceColumn, sourceVersion, value);
            return parameter;
        }

        /// <summary>
        /// Associates an instance of the connection to a command object.
        /// </summary>
        /// <param name="command">An instance of the Db2 command object.</param>
        /// <param name="connection">An instance of the Db2 Connection object.</param>
        protected void PrepareCommand(DbCommand command, DbConnection connection)
        {
            if (command == null)
            {
                throw new ArgumentException("Command object cannot be null.");
            }

            if (connection == null)
            {
                throw new ArgumentException("Connection object cannot be null.");
            }

            command.Connection = connection;
        }

        /// <summary>
        /// Associates an instance of the command object to the currnet transaction and its connection.
        /// </summary>
        /// <param name="command">An Db2 Command object.</param>
        /// <param name="transaction">Db2 Transaction to which this command object needs to be enlisted to.</param>
        protected void PrepareCommand(DbCommand command, DbTransaction transaction)
        {
            if (command == null)
            {
                throw new ArgumentNullException("Command object cannot be null.");
            }

            if (transaction == null)
            {
                throw new ArgumentNullException("Transaction Object cannot be null.");
            }

            this.PrepareCommand(command, transaction.Connection);
            command.Transaction = transaction;
        }

        /// <summary>
        /// Return an instance of the connection wrapper class.
        /// </summary>
        /// <param name="disposeConnection">Whether to dispose the connection or not.</param>
        /// <returns>Returns the instance of the connection wrapper class.</returns>
        protected ConnectionWrapper GetOpenConnection(bool disposeConnection)
        {
            iDB2Connection connection =
                TransactionScopeConnections.GetConnection(this);
            if (connection != null)
            {
                return new ConnectionWrapper(connection, false);
            }

            return new
                ConnectionWrapper(this.GetNewOpenConnection(), disposeConnection);
        }

        /// <summary>
        /// Returns an instance of the Connection Wrapper class.
        /// </summary>
        /// <returns>An instance of the Connection Wrapper.</returns>
        protected ConnectionWrapper GetOpenConnection()
        {
            return this.GetOpenConnection(true);
        }

        /// <summary>
        /// Drieve the parameters for the associted command object.
        /// </summary>
        /// <param name="command">An instance of the command object for which the parameters needs to be derived.</param>
        protected void DeriveParameters(iDB2Command command)
        {
            //// TODO - May need to look for caching to increase performance.
            command.DeriveParameters();
        }

        /// <summary>
        /// Returns a new DataAdapter instance.
        /// </summary>
        /// <param name="behaviour">Behaviour of the data adapter instance.</param>
        /// <returns>A new data adapter instance.</returns>
        protected IDataAdapter GetDataAdapter(UpdateBehavior behaviour)
        {
            iDB2DataAdapter adapter =
                (iDB2DataAdapter)this.providerFactory.CreateDataAdapter();
            if (behaviour == UpdateBehavior.Continue)
            {
                adapter.RowUpdated +=
                    new iDB2RowUpdatedEventHandler(this.OnDb2RowUpdated);
            }

            return adapter;
        }

        #endregion

        #region Helper Calls

        #region Database Transactions

        /// <summary>
        /// Begins a new Db2 transaction of the connection object supplied.
        /// </summary>
        /// <param name="connection">An open connection object.</param>
        /// <returns>An instance of the DB2 Transaction.</returns>
        private iDB2Transaction BeginTransaction(iDB2Connection connection)
        {
            return connection.BeginTransaction();
        }

        /// <summary>
        /// Commits the already open and in execution transaction.
        /// </summary>
        /// <param name="transaction">An active instance of the Db2 transaction object.</param>
        private void CommitTransaction(iDB2Transaction transaction)
        {
            transaction.Commit();
        }

        /// <summary>
        /// Roll backs an already started Db2 Transaction.
        /// </summary>
        /// <param name="transaction">An active transaction object.</param>
        private void RollbackTransaction(iDB2Transaction transaction)
        {
            transaction.Rollback();
        }

        #endregion

        /// <summary>
        /// Event handler for the Row updated event.
        /// </summary>
        /// <param name="sender">Object that generates the event</param>
        /// <param name="rows">Rows which are updated during the update event.</param>
        private void OnDb2RowUpdated(object sender, iDB2RowUpdatedEventArgs rows)
        {
            if ((rows.RecordsAffected == 0) && (rows.Errors != null))
            {
                //// TODO - Need tp put the right message.
                rows.Row.RowError = String.Empty;
                rows.Status = UpdateStatus.SkipCurrentRow;
            }
        }

        /// <summary>
        /// Updates the provided dataset with the necessary values.
        /// </summary>
        /// <param name="behavior">Indicates the update behavior to use.</param>
        /// <param name="dataSet">Dataset instance to which update needs to be done.</param>
        /// <param name="tableName">Table names inside the dataset to which update needs to be effected to.</param>
        /// <param name="insertCommand">An instance of the Db2 Command object to initiate an insert operation.</param>
        /// <param name="updateCommand">An instance of the Db2 Command object to initiate an update operation.</param>
        /// <param name="deleteCommand">An instance of the Db2 Command object to initiate an delete operation.</param>
        /// <param name="updateBatchSize">Size for the updates done in batch mode.</param>
        /// <returns>Returns the number of rows affected by the update.</returns>
        private int DoUpdateDataSet(
            UpdateBehavior behavior, DataSet dataSet, string tableName,
            iDB2Command insertCommand, iDB2Command updateCommand,
            iDB2Command deleteCommand, int? updateBatchSize)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentException("Table Name cannot be null or empty");
            }

            if (dataSet == null)
            {
                throw new ArgumentNullException("Dataset cannot be null");
            }

            if ((insertCommand == null) &&
                (updateCommand == null) &&
                (deleteCommand == null))
            {
                throw new ArgumentNullException("Command Arguements cannot have null values");
            }

            using (iDB2DataAdapter adapter =
                (iDB2DataAdapter)this.GetDataAdapter(behavior))
            {
                //// TODO - I dont understand why we need to dereference the same.
                iDB2DataAdapter adapter2 = adapter;
                if (insertCommand != null)
                {
                    adapter2.InsertCommand = (iDB2Command)insertCommand;
                }

                if (updateCommand != null)
                {
                    adapter2.UpdateCommand = (iDB2Command)updateCommand;
                }

                if (deleteCommand != null)
                {
                    adapter2.DeleteCommand = (iDB2Command)deleteCommand;
                }

                if (updateBatchSize.HasValue)
                {
                    adapter.UpdateBatchSize = updateBatchSize.Value;
                    if (insertCommand != null)
                    {
                        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                    }

                    if (updateCommand != null)
                    {
                        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                    }

                    if (deleteCommand != null)
                    {
                        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
                    }
                }

                try
                {
                    DateTime now = DateTime.Now;
                    int number = adapter.Update(dataSet.Tables[tableName]);
                    return number;
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Loads the dataset using the command object supplied.
        /// </summary>
        /// <param name="command">An instance of the DB2 Command object.</param>
        /// <param name="dataset">An instance of the dataset to which the data is loaded.</param>
        /// <param name="tableNames">Names of the tables.</param>
        private void DoLoadDataSet(
            iDB2Command command, DataSet dataset, string[] tableNames)
        {
            if (tableNames == null)
            {
                throw new
                    ArgumentNullException("Table Names cannot be null when trying to load a dataset");
            }

            if (tableNames.Length == 0)
            {
                throw new
                    ArgumentException("Atleast one table name need to be specified while trying to load the dataset");
            }

            for (int i = 0; i < tableNames.Length; i++)
            {
                if (string.IsNullOrWhiteSpace(tableNames[i]) ||
                    string.IsNullOrEmpty(tableNames[i]))
                {
                    throw new ArgumentException("Tables names provided cannot be empty or null strings");
                }
            }

            using (iDB2DataAdapter adapter =
                (iDB2DataAdapter)this.GetDataAdapter(UpdateBehavior.Standard))
            {
                adapter.SelectCommand = command;
                try
                {
                    string queryString = "Table";
                    for (int j = 0; j < tableNames.Length; j++)
                    {
                        string sourceTable = (j == 0) ? queryString : (queryString + j);
                        adapter.TableMappings.Add(sourceTable, tableNames[j]);
                    }

                    adapter.Fill(dataset);
                }
                catch (Exception)
                {
                    command.Connection.Dispose();
                    GC.SuppressFinalize(command.Connection);
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the Non Query based command objects
        /// </summary>
        /// <param name="command">Db2 Command object.</param>
        /// <returns>Returns the number of rows affected.</returns>
        private int DoExecuteNonQuery(iDB2Command command)
        {
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// Executes the scalar command.
        /// </summary>
        /// <param name="command">Db2 Command object that needs to be executed.</param>
        /// <returns>Returns the result as a scalar object.</returns>
        private object DoExecuteScalar(iDB2Command command)
        {
            return command.ExecuteScalar();
        }

        /// <summary>
        /// Executes a data reader command against the database.
        /// </summary>
        /// <param name="command">An instance of Db2 command object.</param>
        /// <param name="behaviour">Behaviour of the returned data reader instance.</param>
        /// <returns>Returns an instance of the Db2 reader object.</returns>
        private iDB2DataReader DoExecuteReader(
            iDB2Command command, CommandBehavior behaviour)
        {
            return command.ExecuteReader(behaviour);
        }

        /// <summary>
        /// Creates an instance of the Db2 Command object from the command type and text 
        /// provided.
        /// </summary>
        /// <param name="commandType">Type of the command that needs to be created.</param>
        /// <param name="commandText">Command Text for the command object.</param>
        /// <returns>Returns and instance of the Db2 Command object.</returns>
        private iDB2Command CreateCommandByCommandType(
            CommandType commandType, string commandText)
        {
            iDB2Command command =
                (iDB2Command)this.providerFactory.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;
            return command;
        }

        /// <summary>
        /// Configures the supplied DB2 Parameter object with the specified values.
        /// </summary>
        /// <param name="parameter">An instance of the DB2 Parameter object that needs to be configured.</param>
        /// <param name="name">Name of the parameter.</param>
        /// <param name="dbType">Data type of the parameter.</param>
        /// <param name="size">Data size of the parameter object.</param>
        /// <param name="direction">Parameter direction.</param>
        /// <param name="nullable">Is tha parameter value nullable.</param>
        /// <param name="precision">Data precission of the parameter.</param>
        /// <param name="scale">Scale of the parameter value.</param>
        /// <param name="sourceColumn">Source coloumn represented by the parameter.</param>
        /// <param name="sourceVersion">Source version of the parameter.</param>
        /// <param name="value">Value for the paramater.</param>
        private void ConfigureParameter(
            iDB2Parameter parameter, string name, DbType dbType, int size,
            ParameterDirection direction, bool nullable, byte precision,
            byte scale, string sourceColumn, DataRowVersion sourceVersion,
            object value)
        {
            parameter.DbType = dbType;
            parameter.Size = size;
            parameter.Direction = direction;
            parameter.IsNullable = nullable;
            parameter.Scale = scale;
            parameter.SourceColumn = sourceColumn;
            parameter.SourceVersion = sourceVersion;
            parameter.Value = (value == null) ? DBNull.Value : value;
        }

        /// <summary>
        /// Checks whether the command object has the same number of parameters 
        /// as the object value array passed.
        /// </summary>
        /// <param name="command">
        /// Command to which the number of parameters to the checked.
        /// </param>
        /// <param name="values">
        /// Collection of vales that needs to be checked.
        /// </param>
        /// <returns>
        /// True if command and object value array has the same number 
        /// of parameters.
        /// </returns>
        private bool SameNumberofParametersAndValues(
            iDB2Command command, object[] values)
        {
            return (command.Parameters.Count == values.Length) ? true : false;
        }

        /// <summary>
        /// Assign the specific values to the parameters of the command object.
        /// </summary>
        /// <param name="command">
        /// Command object on which parameters needs to be attached to.
        /// </param>
        /// <param name="values">
        /// Value that needs to be attached to the paramater.
        /// </param>
        private void AssignParameterValues(iDB2Command command, object[] values)
        {
            for (int i = 0; i < values.Length; i++)
            {
                iDB2Parameter parameter = command.Parameters[i];
                this.SetParameterValue(command, parameter.ParameterName, values[i]);
            }
        }

        /// <summary>
        /// Validates and returns a provided connection string.
        /// </summary>
        /// <param name="connectionString">Connection string for the database</param>
        /// <returns>A validated connection string</returns>
        private string GetConnectionString(string connectionString)
        {
            try
            {
                iDB2ConnectionStringBuilder connBuilder =
                    new iDB2ConnectionStringBuilder(connectionString);
                return connBuilder.ConnectionString;
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        /// <summary>
        /// Wraps the DB2 Connection object and also graciously implements the IDisposible interface.
        /// </summary>
        protected sealed class ConnectionWrapper : IDisposable
        {
            /// <summary>
            /// Variable to hold a conenction to the underlying database.
            /// </summary>
            private iDB2Connection connection;

            /// <summary>
            /// whether to dispose the connection or not.
            /// </summary>
            private bool disposeConnection;

            /// <summary>
            /// Initializes a new instance of the ConnectionWrapper class
            /// </summary>
            /// <param name="connection">An instance of the DB2 Connection object</param>
            /// <param name="disposeConnection">Boolen value to indicate to dispose or not.</param>
            public ConnectionWrapper(iDB2Connection connection, bool disposeConnection)
            {
                this.connection = connection;
                this.disposeConnection = disposeConnection;
            }

            /// <summary>
            /// Gets the the connection object contained by the connection wrapper instance.
            /// </summary>
            public iDB2Connection Connection
            {
                get { return this.connection; }
            }

            #region IDisposable Members

            /// <summary>
            /// Disposes the underlying Db2 Connection object.
            /// </summary>
            public void Dispose()
            {
                if (this.disposeConnection)
                {
                    if (this.connection.State == ConnectionState.Open)
                    {
                        this.connection.Close();
                    }

                    this.connection.Dispose();
                    GC.SuppressFinalize(this);
                }
            }

            #endregion
        }
    }
}